package DBLayer;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import modelLayer.*;

import java.util.*;

public abstract class DbProduct implements IFDBProduct{

	private Connection con;
	/**creates a new instance of DBProduct*/
	
	public DbProduct() 
	{
		con = DbConnection.getInstance().getDBcon();
	}
	/*
	// get all products
	public ArrayList<Product> getAllProducts(boolean retriveAssociation)
	{
		return miscWhere("", retriveAssociation); //retrives multiple tuples from the database
	}
	*/
	//get a product with productId
	public Product findProduct(int productId, boolean retriveAssociation)
	{
		String wClause = " productId = " + productId;
		return singleWhere(wClause, retriveAssociation);
	}
	
	//find a product with name
	public Product findProduct(String name, boolean retriveAssociation)
	{
		String wClause = "name like '%" + name + "%'";
		System.out.println("Search Product " + wClause);
		return singleWhere(wClause, retriveAssociation);
	}
	
	//add a new product
	private Product singleWhere(String wClause, boolean retriveAssociation) {
		// TODO Auto-generated method stub
		return null;
	}

	public int insertProduct(Product prod) 
	{
		int rc = -1;
		String query = "INSERT INTO product(productId, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, productType, supName, locationId, productDescription) VALUES(prod.getProductId() + ",' " + prod.getName() + "'," + prod.getPurchasePrice() + "'," + prod.getSalesPrice() + "," + prod.getRentPrice() +",'" + prod.getCountryOfOrigin() + "','" + prod.getProductType() + "','" + prod.getSupName() + "'," + prod.getLocationId() + ",'" + prod.getProductDescription() + "')";
		System.out.println("Insert: " + query); 
		
			//insert new product + suppliername + locationId
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		
		return(rc);
	}
	
	//update product
	public int updateProduct(Product prod)
	{
		Product pObj = prod;
		int rc = -1;
		
		String query = "UPDATE product SET " + 
				//"productId = " + pObj.getProductId() + ", " +
				"name = '" + pObj.getName() + "', " + 
				"purchasePrice = " + pObj.getPurchasePrice() + ", " +
				"salesPrice = " + pObj.getSalesPrice() + ", " +
				"rentPrice = " + pObj.getRentPrice() + ", " + 
				"countryOfOrigin = '" + pObj.getCountryOfOrigin() + "', " + 
				"productType = '" + pObj.getProductType() + "', " + 
				"supName = '" + pObj.getSupName() + "' " + 
				"locationId = " + pObj.getLocationId() + ", " +
				"productDescription = '" + pObj.getProductDescription() + "' " + 
				" WHERE productId = " + pObj.getProductId() + "";
		
		System.out.println("Update query: " + query);
		return (rc);
	}
	
	/*//select more than one product
	private ArrayList<Product> miscWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		ArrayList<Product> list = new ArrayList<Product>();
		
		//read the product from the database
		String query = buildQuery(wClause);
		Statement stmt = con.createStatement();
		stmt.setQueryTimeout(5);
		results = stmt.executeQuery(query);
		
		while(results.next())
		{
			Product pObj = new Product(0, query, 0, 0, 0, query, query, query, 0, query);
			pObj = buildProduct(results);
			list.add(pObj);
		}
		
		stmt.close();
		return list;
	}*/
	/*
	//method to select one product
	private Product singleWhere(String wClause, boolean retriveAssociation)
	{
		ResultSet results;
		Product pObj = new Product(0, wClause, 0, 0, 0, wClause, wClause, wClause, 0, wClause);
		Location lObj = new Location(0, wClause, wClause, wClause, 0, 0)
		String query = buildQuery(wClause);
		System.out.println(query);
		
		//read the product from the database
		Statement stmt = con.createStatement();
		stmt.setQueryTimeout(5);
		
		if(results.next())
		{
			pObj = buildProduct(results);
			stmt.close();
			if(pObj.getLocation() != null)
			{
				DbLocation dbObj = new DbLocation();
				lObj = dbObj.findDepartment(pObj.getLocationId().getLocation(), false);
				pObj.setLocation(lObj);				
			}
		}
		else
		{
			pObj = null;
		}
	
	return pObj;
	}
	*/
	//method to build the query
	private String buildQuery(String wClause)
	{
		String query = "SELECT productId, name, purchasePrice, salesPrice, rentPrice, countryOfOrigin, productType, supName, locationId, productDescription";
		
		if(wClause.length() > 0)
			query = query + "WHERE " + wClause;
		
		return query;
	}
	
	//method to build a product object
	private Product buildProduct(ResultSet results)
	{
		Product pObj = new Product(0, null, 0, 0, 0, null, null, null, 0, null);
		try
		{
			pObj.setProductId(results.getInt("productId"));
			pObj.setName(results.getString("name"));
			pObj.setPurchasePrice(results.getDouble("purchasePrice"));
		}
		catch(Exception e)
		{
			System.out.println("error in building the employee object");
		}
		return pObj;
	}
	/*
		private Employee buildEmployee(ResultSet results)
	      {   Employee empObj = new Employee();
	          try{ // the columns from the table emplayee  are used
	                empObj.setFname(results.getString("fname"));
	                empObj.setMinit(results.getString("minit"));
	                empObj.setLname(results.getString("lname"));
	                empObj.setSsn(results.getString("ssn"));
	                empObj.setBdate(results.getString("bdate"));
	                empObj.setAddress(results.getString("address"));
	                empObj.setSex(results.getString("sex"));
	                empObj.setSalary(results.getDouble("salary"));
	                empObj.setSupervisor(new Employee(results.getString("superssn")));
	                empObj.setDepartment(new Department(results.getInt("dno")));
	          }
	         catch(Exception e)
	         {
	             System.out.println("error in building the employee object");
	         }
	         return empObj;
	      }*/
		
		@Override
		public Product findProductId(int productId, boolean retriveAssociation) {
			// TODO Auto-generated method stub
			return null;
		}

		@Override
		public Product searchProductId(int productId, boolean b) {
			// TODO Auto-generated method stub
			return null;
		}
}


